﻿using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using PHMEE.EnergyEfficiency;
using PHMEE.TDengineInfrastructure;
using PHMEE;
using Volo.Abp;
using PHMEE.ToolKits;
using System;

/// <summary>
/// 流量计数据操作
/// </summary>
public class MassFlowMeterRepository : BaseTdRepository, IMassFlowMeterRepository
{
    private const DbPrefixEnum DbPrefix = DbPrefixEnum.Ee;
    private const string SuperTableName = PhmeeConst.TdSuperTables.Flowmeter;
    private readonly IOptionsSnapshot<TDengineOptions> tdengineOptions;
    private readonly ILogger<MassFlowMeterRepository> _logger;
    private readonly IVesselRepository _vesselRepository;
    private readonly IFlowmeterTemplateRepository _flowmeterTemplateRepository;

    public MassFlowMeterRepository(
        IOptionsSnapshot<TDengineOptions> tdengineOptions,
        ILogger<MassFlowMeterRepository> logger,
        IVesselRepository vesselRepository,
        IFlowmeterTemplateRepository flowmeterTemplateRepository) : base(tdengineOptions)
    {
        this.tdengineOptions = tdengineOptions;
        _logger = logger;
        _vesselRepository = vesselRepository;
        _flowmeterTemplateRepository = flowmeterTemplateRepository;
    }

    /// <summary>
    /// 获取指定船舶所有流量计的最新数据
    /// </summary>
    /// <param name="vesselId"></param>
    /// <returns></returns>
    /// <exception cref="UserFriendlyException"></exception>
    public async Task<List<MassFlowmeter>> GetAllFlowmeterAsync(Guid vesselId)
    {
        var results = new List<MassFlowmeter>();

        //数据库名称
        string dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

        //船舶挂载的模版
        var mode = await _vesselRepository.GetTemplateModeByVesselIdAsync(vesselId);
        if (mode == null)
        {
            throw new UserFriendlyException("未找到对应的流量计模版");
        }

        //获取所有TD子表
        var subTableNames = await _flowmeterTemplateRepository.GetFlowmeterNames(mode.Value);

        if (!subTableNames.Any())
        {
            throw new UserFriendlyException("未获取到流量计数据！");
        }

        //获取流量计所有属性
        var columns = TdTableHelper.GetColumnsForSuperTable(SuperTableName);
        var columnString = string.Join(",", columns);

        foreach (var subTableName in subTableNames)
        {
            var SumOilValue = await GetTotalOilValueSumAsync(dbName, subTableName);

            string sql = $"SELECT {columnString} FROM {dbName}.{subTableName} ORDER BY ts DESC LIMIT 1;";
            using var command = TDConnection.CreateCommand();
            command.CommandText = sql;

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var data = new MassFlowmeter
                {
                    FmName = subTableName,
                    Timestamp = reader.GetDateTime(reader.GetOrdinal("ts")).ToUniversalTime(),
                    Instant = reader.GetFloat(reader.GetOrdinal("instant")),
                    OilValue = reader.GetFloat(reader.GetOrdinal("oil_value")),
                    TotalValue = SumOilValue,//OilValue累计和赋值给TotalValue
                    Temperature = reader.GetFloat(reader.GetOrdinal("temperature")),
                    Density = reader.GetFloat(reader.GetOrdinal("density")),
                    FuelType = (FuelType)reader.GetInt32(reader.GetOrdinal("fuel_type")),
                };
                results.Add(data);
            }
        }

        return results;
    }


    /// <summary>
    ///  获取指定日期范围内的所有流量计数据
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="startDateTime"></param>
    /// <param name="endDateTime"></param>
    /// <returns></returns>
    /// <exception cref="UserFriendlyException"></exception>
    public async Task<Dictionary<string, List<MassFlowmeter>>> GetFlowmeterByDateAsync(Guid vesselId, DateTime startDateTime, DateTime endDateTime)
    {
        var resultDict = new Dictionary<string, List<MassFlowmeter>>();
        string dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

        var mode = await _vesselRepository.GetTemplateModeByVesselIdAsync(vesselId);
        if (mode == null)
        {
            throw new UserFriendlyException("未找到对应的流量计模版");
        }

        var subTableNames = await _flowmeterTemplateRepository.GetFlowmeterNames(mode.Value);

        if (!subTableNames.Any())
        {
            throw new UserFriendlyException("未获取到流量计数据！");
        }

        var columns = TdTableHelper.GetColumnsForSuperTable(SuperTableName);
        var columnString = string.Join(",", columns);

        foreach (var subTableName in subTableNames)
        {
            var subTableDataList = new List<MassFlowmeter>();

            string sql = $"SELECT {columnString} FROM {dbName}.{subTableName} WHERE ts >= '{startDateTime.PhmeeToUtcFormatDateTimeString()}' AND ts < '{endDateTime.PhmeeToUtcFormatDateTimeString()}';";
            using var command = TDConnection.CreateCommand();
            command.CommandText = sql;

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var data = new MassFlowmeter
                {
                    FmName = subTableName,
                    Timestamp = reader.GetDateTime(reader.GetOrdinal("ts")).ToUniversalTime(),
                    Instant = reader.GetFloat(reader.GetOrdinal("instant")),
                    OilValue = reader.GetFloat(reader.GetOrdinal("oil_value")),
                    TotalValue = reader.GetFloat(reader.GetOrdinal("total_value")),
                    Temperature = reader.GetFloat(reader.GetOrdinal("temperature")),
                    Density = reader.GetFloat(reader.GetOrdinal("density")),
                    FuelType = (FuelType)reader.GetInt32(reader.GetOrdinal("fuel_type")),
                };
                subTableDataList.Add(data);
            }
            var sheetName = subTableName switch
            {
                "fm_001" => (mode.Value == FlowMeterModeEnum.Template2 || mode.Value == FlowMeterModeEnum.Template4) ? "MEI&AEI" : "MEI",
                "fm_002" => (mode.Value == FlowMeterModeEnum.Template2 || mode.Value == FlowMeterModeEnum.Template4) ? "MER&AER" : "MER",
                "fm_003" => "AEI",
                "fm_004" => "AER",
                "fm_005" => "BI",
                "fm_006" => "BR",
                _ => subTableName
            };

            resultDict[sheetName] = subTableDataList;
        }

        return resultDict;
    }

    /// <summary>
    /// 获取主机、副机、锅炉数据，合并时返回主副机数据
    /// </summary>
    /// <param name="vesselId"></param>
    /// <returns></returns>
    public async Task<List<DeviceFuelConsumption>> GetDeviceFuelConsumptionAsync(Guid vesselId)
    {

        var flowmeters = await GetAllFlowmeterAsync(vesselId);
        if (flowmeters == null || !flowmeters.Any())
        {
            throw new UserFriendlyException("未获取到流量计数据！");
        }

        string dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);
        var results = new List<DeviceFuelConsumption>();

        //最新一条数据的时间
        var latestTimestamp = flowmeters.Max(f => f.Timestamp) ?? DateTime.UtcNow;
        //UTC时间0点
        var startOfDay = latestTimestamp.PhmeeToStartOfDayUtc().PhmeeToUtcFormatDateTimeString();

        var flowmeterGroups = flowmeters.GroupBy(f => f.FmName).ToDictionary(g => g.Key, g => g.ToList());

        var mode = await _vesselRepository.GetTemplateModeByVesselIdAsync(vesselId);
        if (mode == null)
        {
            throw new UserFriendlyException("未找到对应的流量计模版");
        }
        var mappings = await LoadFlowmeterMappingsFromDatabaseAsync(mode.Value);

        await ProcessFlowmeterMappingsAsync(dbName, flowmeterGroups, startOfDay, results, mappings, mode.Value);

        return results;

    }

    /// <summary>
    /// 根据流量计模板模式，加载流量计与设备的映射关系
    /// </summary>
    /// <param name="mode"></param>
    /// <returns></returns>
    private async Task<List<FlowmeterMapping>> LoadFlowmeterMappingsFromDatabaseAsync(FlowMeterModeEnum mode)
    {
        var templates = await _flowmeterTemplateRepository.GetTemplatesByModeAsync(mode);

        var mappings = new List<FlowmeterMapping>();
        foreach (var template in templates)
        {
            var mapping = mappings.FirstOrDefault(m => m.DeviceName == template.DeviceType);
            if (mapping == null)
            {
                mapping = new FlowmeterMapping { DeviceName = template.DeviceType };
                mappings.Add(mapping);
            }

            if (template.IsInlet)
            {
                mapping.InletKeys.Add(template.FlowmeterName);
            }
            else
            {
                mapping.OutletKeys.Add(template.FlowmeterName);
            }
        }

        return mappings;
    }

    /// <summary>
    /// 计算每个设备（主机、副机、锅炉等）的油耗数据
    /// </summary>
    /// <param name="dbName"></param>
    /// <param name="flowmeterGroups"></param>
    /// <param name="startOfDay"></param>
    /// <param name="results"></param>
    /// <param name="mappings"></param>
    /// <param name="mode"></param>
    /// <returns></returns>
    private async Task ProcessFlowmeterMappingsAsync(string dbName, Dictionary<string, List<MassFlowmeter>> flowmeterGroups, string startOfDay, List<DeviceFuelConsumption> results, List<FlowmeterMapping> mappings, FlowMeterModeEnum mode)
    {
        bool isCombinedMode = mode == FlowMeterModeEnum.Template2 || mode == FlowMeterModeEnum.Template4;

        foreach (var mapping in mappings)
        {
            float inletTodaySum = 0, outletTodaySum = 0;
            float inletTotalSum = 0, outletTotalSum = 0;

            foreach (var inletKey in mapping.InletKeys)
            {
                inletTodaySum += await GetOilValueSumFromTimeAsync(dbName, inletKey, startOfDay);
                inletTotalSum += await GetTotalOilValueSumAsync(dbName, inletKey);
            }

            foreach (var outletKey in mapping.OutletKeys)
            {
                outletTodaySum += await GetOilValueSumFromTimeAsync(dbName, outletKey, startOfDay);
                outletTotalSum += await GetTotalOilValueSumAsync(dbName, outletKey);
            }

            var instantOil = mapping.CalculateInstantOil(flowmeterGroups);

            var latestInletData = mapping.InletKeys
                .SelectMany(key => flowmeterGroups.ContainsKey(key) ? flowmeterGroups[key] : new List<MassFlowmeter>())
                .OrderByDescending(f => f.Timestamp)
                .FirstOrDefault();

            results.Add(new DeviceFuelConsumption
            {
                DeviceName = mapping.DeviceName,
                DayOilValue = inletTodaySum - outletTodaySum > 0 ? inletTodaySum - outletTodaySum : null,
                SumOilValue = inletTotalSum - outletTotalSum > 0 ? inletTotalSum - outletTotalSum : null,
                Timestamp = latestInletData?.Timestamp ?? null,
                Instant = instantOil > 0 ? instantOil : null,
                TotalValue = latestInletData?.TotalValue,
                Temperature = latestInletData?.Temperature,
                Density = latestInletData?.Density,
                FuelType = latestInletData?.FuelType
            });
        }

        // 合并模式处理 MeAndAe 和 MainEngine 数据
        if (isCombinedMode)
        {
            await AddMainEngineDataAsync(results, mappings, dbName, startOfDay);
        }
    }

    /// <summary>
    /// 合并模式下添加主机设备数据
    /// </summary>
    /// <param name="results"></param>
    /// <param name="mappings"></param>
    /// <param name="dbName"></param>
    /// <param name="startOfDay"></param>
    /// <returns></returns>
    private async Task AddMainEngineDataAsync(List<DeviceFuelConsumption> results, List<FlowmeterMapping> mappings, string dbName, string startOfDay)
    {
        var combinedMapping = mappings.FirstOrDefault(m => m.DeviceName == DeviceFuelEnum.MainAndAssist);
        var auxiliaryMapping = mappings.FirstOrDefault(m => m.DeviceName == DeviceFuelEnum.AuxiliaryEngine);

        if (combinedMapping != null)
        {
            var combinedData = results.FirstOrDefault(r => r.DeviceName == DeviceFuelEnum.MainAndAssist);

            if (auxiliaryMapping != null)
            {
                float mainEngineDayOilValue = combinedData?.DayOilValue ?? 0;
                float mainEngineOilValueSum = combinedData?.SumOilValue ?? 0;

                var auxiliaryData = results.FirstOrDefault(r => r.DeviceName == DeviceFuelEnum.AuxiliaryEngine);

                // 计算 MainEngine 的即时值
                float mainEngineInstant = (combinedData?.Instant ?? 0) - (auxiliaryData?.Instant ?? 0);

                // 计算 MainEngine 的 DayOilValue 和 SumOilValue
                foreach (var inletKey in auxiliaryMapping.InletKeys)
                {
                    mainEngineDayOilValue -= await GetOilValueSumFromTimeAsync(dbName, inletKey, startOfDay);
                    mainEngineOilValueSum -= await GetTotalOilValueSumAsync(dbName, inletKey);
                }

                foreach (var outletKey in auxiliaryMapping.OutletKeys)
                {
                    mainEngineDayOilValue += await GetOilValueSumFromTimeAsync(dbName, outletKey, startOfDay);
                    mainEngineOilValueSum += await GetTotalOilValueSumAsync(dbName, outletKey);
                }

                // 新增 MainEngine 数据
                results.Add(new DeviceFuelConsumption
                {
                    DeviceName = DeviceFuelEnum.MainEngine,
                    DayOilValue = mainEngineDayOilValue > 0 ? mainEngineDayOilValue : null,
                    SumOilValue = mainEngineOilValueSum > 0 ? mainEngineOilValueSum : null,
                    Instant = mainEngineInstant > 0 ? mainEngineInstant : null,
                    Timestamp = combinedData?.Timestamp,
                    TotalValue = combinedData?.TotalValue,
                    Temperature = combinedData?.Temperature,
                    Density = combinedData?.Density,
                    FuelType = combinedData?.FuelType
                });
            }
        }
    }

    /// <summary>
    /// 查询指定流量计从某个时间点开始的油值总和
    /// </summary>
    /// <param name="dbName"></param>
    /// <param name="flowmeterName"></param>
    /// <param name="startTime"></param>
    /// <returns></returns>
    private async Task<float> GetOilValueSumFromTimeAsync(string dbName, string flowmeterName, string startTime)
    {
        var sql = $@"SELECT SUM(oil_value) FROM {dbName}.{flowmeterName} WHERE ts >= '{startTime}';";

        using var command = TDConnection.CreateCommand();
        command.CommandText = sql;

        var result = await command.ExecuteScalarAsync();
        return result != DBNull.Value ? Convert.ToSingle(result) : 0f;
    }
    /// <summary>
    /// 查询指定流量计的油值总和。
    /// </summary>
    /// <param name="dbName"></param>
    /// <param name="flowmeterName"></param>
    /// <returns></returns>
    private async Task<float> GetTotalOilValueSumAsync(string dbName, string flowmeterName)
    {
        var sql = $@"SELECT SUM(oil_value) FROM {dbName}.{flowmeterName};";

        using var command = TDConnection.CreateCommand();
        command.CommandText = sql;

        var result = await command.ExecuteScalarAsync();
        return result != DBNull.Value ? Convert.ToSingle(result) : 0f;
    }
}
